module.exports = {
  // 获取课程列表（带教师信息和报名人数）
  GET_COURSES: `
    SELECT 
      c.*,
      u.real_name as teacher_name,
      COUNT(DISTINCT ce.student_id) as enrolled_students,
      GROUP_CONCAT(DISTINCT ct.name) as tags,
      GROUP_CONCAT(DISTINCT cat.id) as category_ids,
      GROUP_CONCAT(DISTINCT cat.name) as category_names
    FROM courses c
    LEFT JOIN users u ON c.teacher_id = u.id
    LEFT JOIN course_enrollments ce ON c.id = ce.course_id
    LEFT JOIN course_tag_relations ctr ON c.id = ctr.course_id
    LEFT JOIN course_tags ct ON ctr.tag_id = ct.id
    LEFT JOIN category_course_relations ccr ON c.id = ccr.course_id
    LEFT JOIN categories cat ON ccr.category_id = cat.id
    WHERE c.status = 'published'
    GROUP BY c.id
    ORDER BY c.created_at DESC
  `,

  // 获取课程详情
  GET_COURSE_DETAIL: `
    SELECT 
      c.*,
      u.real_name as teacher_name,
      u.avatar_url as teacher_avatar,
      tp.introduction as teacher_intro,
      COUNT(DISTINCT ce.student_id) as enrolled_students,
      GROUP_CONCAT(DISTINCT ct.name) as tags,
      GROUP_CONCAT(DISTINCT cf.feature) as features
    FROM courses c
    LEFT JOIN users u ON c.teacher_id = u.id
    LEFT JOIN teacher_profiles tp ON u.id = tp.user_id
    LEFT JOIN course_enrollments ce ON c.id = ce.course_id
    LEFT JOIN course_tag_relations ctr ON c.id = ctr.course_id
    LEFT JOIN course_tags ct ON ctr.tag_id = ct.id
    LEFT JOIN course_features cf ON c.id = cf.course_id
    WHERE c.id = ? AND c.status = 'published'
    GROUP BY c.id
  `,

  // 创建课程
  CREATE_COURSE: `
    INSERT INTO courses (
      title, short_desc, description, image_url, 
      teacher_id, level, price, original_price,
      duration, max_students, schedule, status
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `,

  // 添加课程特点
  ADD_COURSE_FEATURE: `
    INSERT INTO course_features (course_id, feature) VALUES (?, ?)
  `,

  // 添加课程标签关联
  ADD_COURSE_TAG: `
    INSERT INTO course_tag_relations (course_id, tag_id) VALUES (?, ?)
  `,

  // 报名课程
  ENROLL_COURSE: `
    INSERT INTO course_enrollments (
      course_id, student_id, status, payment_status, 
      payment_amount, notes
    ) VALUES (?, ?, ?, ?, ?, ?)
  `,

  // 查询学生的课程
  GET_STUDENT_COURSES: `
    SELECT 
      c.*,
      u.real_name as teacher_name,
      ce.status as enrollment_status,
      ce.payment_status
    FROM course_enrollments ce
    JOIN courses c ON ce.course_id = c.id
    JOIN users u ON c.teacher_id = u.id
    WHERE ce.student_id = ?
    ORDER BY ce.enrollment_date DESC
  `,

  // 查询教师的课程
  GET_TEACHER_COURSES: `
    SELECT 
      c.*,
      COUNT(DISTINCT ce.student_id) as enrolled_students
    FROM courses c
    LEFT JOIN course_enrollments ce ON c.id = ce.course_id
    WHERE c.teacher_id = ?
    GROUP BY c.id
    ORDER BY c.created_at DESC
  `
}; 